package org.weso.lodin.dao;

import java.io.IOException;
import java.io.InputStream;
import java.util.LinkedList;
import java.util.List;

import javax.net.ssl.SSLEngineResult.Status;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.weso.lodin.exceptions.LodinModelException;
import org.weso.lodin.generated.CountryEnum;
import org.weso.lodin.generated.Dataset;
import org.weso.lodin.generated.TypeTable;
import org.weso.lodin.generated.TypeValues;
import org.weso.lodin.to.ObservationTO;
import org.weso.lodin.to.ScoreTO;
import org.weso.lodin.utils.POIUtils;
import org.weso.lodin.utils.StatusUtils;

public class ScoreDAOImpl{

	protected static Logger logger = Logger.getLogger(ScoreDAOImpl.class);
	private HSSFWorkbook wb;

	public ScoreDAOImpl(InputStream data){
		try {			
			this.wb = new HSSFWorkbook(data);
			logger.info("Loading workbook of scores");
		} catch (IOException e) {
			e.printStackTrace();
			throw new LodinModelException(e, "Exception loading workbook of observations");
		}
	}



	public List<ScoreTO> extractObservationsByDataset(String sheetName) {
		List<ScoreTO> observations = new LinkedList<ScoreTO>();
		HSSFSheet sheet = wb.getSheet(sheetName);
		FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
		logger.info("Loading observations from sheet: "+sheetName+" ");
		HSSFRow start = sheet.getRow(0);
		for(int i = 2; i<start.getPhysicalNumberOfCells(); i++){
			String ref = start.getCell(i).getStringCellValue();			
			int count = 0;
			for(int rowIndex = 1; rowIndex<62; rowIndex++){
				HSSFRow row = sheet.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				String country = POIUtils.extractValue(
						row.getCell(ObservationDAOImpl.encode("B")), 
						evaluator);
				String value = POIUtils.extractValue(row.getCell(i), evaluator);
				String year = "2011";
				//System.out.println("Country: "+country+" value: "+value+" ref: "+ref);
				ScoreTO score = new ScoreTO(country, value, year, ref, sheetName);
				score.setStatus(StatusUtils.NORMAL);
				observations.add(score);
				count++;
			}
			logger.debug("REF: "+ref+"-"+count);
		}
		return observations;
	}






}
